<!--
PAGE DOCUMENTATION:
==================
This is a library containig all mySql manipulation functions for the application.
See inline documentation for specific functions.
-->

<?php

	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$user - user id of the user that removed the app.
	* @ purpose:	function deletes the user's row from the sessions DB.
	* @ return:		1 for success, 0 for failure.
	*/
	function removeSessionKey($sqlDb,$user)
	{
		$query = "DELETE FROM sessions WHERE user_id=$user";
		if ($statment = $sqlDb->stmt_init()) {
			if ($statment->prepare($query)) {
				if ($statment->execute()) {
					$rowsCahnged = $sqlDb->affected_rows;
					$statment->close();
					if ($rowsCahnged != 1) {
						return 0;
					} else {
						return 1;
					}
				}
			}
			$statment->close();
		}
		return 0;
	}
	
	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$user - user id of the user that added the app.
	*				$session_key - the infinite session key of the user.
	* @ purpose:	function inserts the wanted new line to the sessions DB.
	* @ return:		the index of the row inserted for success, or FALSE for error.
	*/
	function storeSessionKey($sqlDb,$user,$session_key)
	{
		$query = "INSERT sessions (user_id,session_key) VALUES ($user,'$session_key')";
		$statment = $sqlDb->stmt_init();
		if (!$statment) {
			return false;
		}
		if(!($statment->prepare($query))) {
			return false;
		}
		$res = $statment->execute();
		$statment->close();
		if ($res) {
			return $sqlDb->insert_id;
		} else {
			return $res;
		}
	}

	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$user_id - the id of the user
	*				$messageType - the kind of messages wanted.
	*				supported message types: "recorded", "unread", "sent"
	* @ purpose:	This function returns the number of $messageType
	*				messages the user has.
	* @ return:		the number of messages or -1 if failed.
	*/
	function countMessages($sqlDb,$user_id,$messageType)
	{
		$result = 0;
		if (!(strcmp($messageType,"recorded"))) {
			$query = "SELECT (messages_data.index) FROM messages_data WHERE recorder_id=$user_id and recipient_id=0";
		} else if (!(strcmp($messageType,"unread"))) {
			$query = "SELECT (messages_data.index) FROM messages_data WHERE recipient_id=$user_id and is_read=0";
		} else if (!(strcmp($messageType,"sent"))) {
			$query = "SELECT (messages_data.index) FROM messages_data WHERE recorder_id=$user_id and recipient_id<>0";
		} else {
			return -1;
		}
		if ($statment = $sqlDb->stmt_init()) {
			if ($statment->prepare($query)) {
				if ($statment->execute()) {
					if ($statment->bind_result($index)) {
						while ($statment->fetch())
						{
							$result = $result + 1;
						}
						$statment->close();
						return $result;
					}
				}
			}
			$statment->close();
		}
		return -1;
	}
	
	/*
	* @ params: 	$sqlDb - mysqli database object
	*				the data about the row to insert (except for the autofill columns):
	*				$message_name,$recorder_id,$recipient_id,$filename
	* @ purpose:	function inserts the wanted new line to the DB.
	* @ return:		the index of the row inserted for success, or FALSE for error.
	*/
	function enterNewMsgRow($sqlDb,$message_name,$recorder_id,$recipient_id,$filename)
	{
		$query = "INSERT messages_data (message_name,recorder_id,recipient_id,is_read,filename) VALUES ('$message_name',$recorder_id,$recipient_id,0,'$filename')";
		if ($statment =  $sqlDb->stmt_init()) {
			if ($statment->prepare($query)) {
				if ($res = $statment->execute()) {
					$statment->close();
					if ($res) {
						return $sqlDb->insert_id;
					} else {
						return FALSE;
					}
				}
			}
			$statment->close();
		}
		return FALSE;
	}
	
	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$dataArr - an (indexed) array that contains rows with the following
	*				data columns: message_name, recorder_id, recipient_id, filename.
	* @ purpose:	function inserts all rows of $dataArr to the DB ('messages_data') in
	*				one query.
	* @ return:		0 for failure, number of rows entered for success.
	*/
	function enterSeveralMsgRow($sqlDb,$dataArr)
	{
		$query = "INSERT messages_data (message_name,recorder_id,recipient_id,is_read,filename) VALUES ";
		for ($i=0;$i<count($dataArr);$i++) {
			if ($i>0) {
				$query = $query.",";
			}
			$query = $query."('".$dataArr[$i]['message_name']."',".$dataArr[$i]['recorder_id'].",".$dataArr[$i]['recipient_id'].",0,'".$dataArr[$i]['filename']."')";
		}
		if ($statment =  $sqlDb->stmt_init()) {
			if ($statment->prepare($query)) {
				if ($res = $statment->execute()) {
					if ($res) {
						$newRows = $sqlDb->affected_rows;
						$statment->close();
						return $newRows;
					} else {
						$statment->close();
						return 0;
					}
				}
			}
			$statment->close();
		}
		return 0;
	}

	/*
	* @ params: 	$message_name - message name
	*				$user_id - ID of the message recorder
	*				$recipientsArr - an array with all the recipients user IDs
	*					(the recipients this message will be sent to).
	*				$filename - the name of the file of the message.
	* @ purpose:	function creates an array with a row for each recipient in 
	*				$recipientsArr that contains the following data:
	*				message name | recorder id | recipient id | file name.
	* @ return:		an array as described on purpose.
	*/
	function createMsgDataArr($message_name,$user_id,$recipientsArr,$filename)
	{
		for ($i=0;$i<count($recipientsArr);$i++) {
			$dataArr[$i]['message_name'] = $message_name;
			$dataArr[$i]['recorder_id'] = $user_id;
			$dataArr[$i]['recipient_id'] = $recipientsArr[$i];
			$dataArr[$i]['filename'] = $filename;
		}
		return $dataArr;
	}
	
	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$table - 'sessions' or 'messges_data' - the appropriate table.
	*				$value - the value accoring to which the needed row will be found.
	*						can be the row's index (for 'messges_data') or user_id (for 'sessions').
	*				$columnName - the name of the column its data we need.
	* @ purpose:	function gets the specified data from the DB.
	* @ return:		the value in specified row and column, or -13 for error.
	*				(because there are no negative values in "messages_data", 
	*				and there are negative values up to -12 in "sessions".)
	*/
	function getColumnData($sqlDb,$table,$value,$columnName) 
	{
		if (!strcmp($table,"sessions")) {
			$query = "SELECT * FROM $table WHERE user_id=$value";
		} elseif (!strcmp($table,"messages_data")) {
			$query = "SELECT * FROM $table WHERE messages_data.index=$value";
		}
		if(!($result = $sqlDb->query($query))) {
			return -13;
		}
		if(!($row = $result->fetch_assoc())) {
			return -13;
		}
		$result->close();
		return $row["$columnName"];
	}	

	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$messageGMTTime - the time the message was recorded in GMT
	*				$offsetInHours - the user's offset from GMT in hours
	* @ purpose:	Function calculates the time of the message according to the user's
	*				time offset from GMT.
	* @ return:		-1 if an error occured, the time if succeeds.
	*/
	function makeUserTimeFromGMT($sqlDb,$messageGMTTime,$offsetInHours)
	{
		if (!$offsetInHours) {
			return $messageGMTTime;
		} else {
			$offSet = floor(abs($offsetInHours));
			// see if it is an offset of x.5:
			if (strstr("'".$offsetInHours."'",".")) {
				$minuetsOffset = 30;
			} else {
				$minuetsOffset = 0;
			}
			if ($offsetInHours < 0) {
				$sign = "-";
			} else {
				$sign = NULL;
			}
			$resultColumn = "ADDTIME('".$messageGMTTime."','".$sign."0 ".$offSet.":".$minuetsOffset.":0')";
			$query = "SELECT ".$resultColumn;
			if(!($result = $sqlDb->query($query))) {
				return -1;
			} elseif (!($row = $result->fetch_assoc())){
				return -1;
			} else {
				return $row[$resultColumn];
			}
		}
	}
	
	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$lineIndicator - 
	*				$offsetInHours - 
	*						can be the row's index (for 'messges_data') or user_id (for 'sessions').
	* @ purpose:	
	* @ return:		
	*/
	/*
	function getMessageUserTime($sqlDb,$lineIndicator,$offsetInHours) 
	{
		// get message time from DB 
		$messageGMTTime = getColumnData($sqlDb,"messages_data",$lineIndicator,"time");
		if ($messageGMTTime==-13) {
			return -1;
		} else {
			// add the offset to the time
			return makeUserTimeFromGMT($sqlDb,$messageGMTTime,$offsetInHours);
		}
	}
	*/	

	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$index - the index of the message that we want data about.
	* @ purpose:	function gets full data of a single row from the DB.
	* @ return:		the data in specified row, or -1 for error.
	*/
	function getmessageFullData($sqlDb,$index)
	{
		$query = "SELECT * FROM messages_data WHERE messages_data.index=$index";
		$result = $sqlDb->query($query);
		if ($result==-1) {
			return -1;
		}
		$row = $result->fetch_assoc();
		return $row;
	}

	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$filename - a file name.
	* @ purpose:	function checks if there are any other messages
	*				that use the same file (have the same filename value).
	* @ return:		1 if there are no other messages with that filename (delete is needed),
	*				0 if there are. returns -1 for failure.
	*/
	function checkFileDeleteNeeded($sqlDb,$filename)
	{
		$res = 0;
		$query = "SELECT * FROM messages_data WHERE filename=\"$filename\"";
		if ($statment = $sqlDb->stmt_init()) {
			if ($statment->prepare($query)) {
				if ($statment->execute()) {
					if ($statment->store_result()) {
						$row_cnt = $statment->num_rows;
						$statment->free_result();
						$statment->close();
						if ($row_cnt==0) {
							return 1;
						} elseif ($row_cnt>0) {
							return 0;
						}
					}
				}
			}
			$statment->close();
		}
		return -1;
	}

	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$index - the index of the line to be modified.
	* @ purpose:	function deletes the specified row ($index)
	*				from the DB.
	* @ return:		1 for success and 0 if error occured.
	*/
	function deleteMessageData($sqlDb,$index)
	{
		$query = "DELETE FROM messages_data WHERE messages_data.index=$index";
		if ($statment = $sqlDb->stmt_init()) {
			if ($statment->prepare($query)) {
				if ($statment->execute()) {
					$rowsCahnged = $sqlDb->affected_rows;
					if ($rowsCahnged == 1) {
						$statment->close();
						return 1;
					}
				}
			}
			$statment->close();
		}
		return 0;
	}

	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$user_id - the user id of the user who's messages will be deleted.
	* @ purpose:	function deletes all the user's messages (received and recorded)
	*				from the DB. It also deletes the files, if there is no other
	*				reference to them (from the DB).
	* @ return:		1 for success and 0 if error occured.
	*/
	function deleteAllUserMessages($sqlDb,$user_id)
	{
		$res = 0;
		$messagesNumber=0;
		//get all messages of the user (recorded and received):
		$query = "SELECT filename FROM messages_data WHERE (recorder_id=$user_id and recipient_id=0) or recipient_id=$user_id";
		if ($statment = $sqlDb->stmt_init()) {
			if ($statment->prepare($query)) {
				if ($statment->execute()) {
					if ($statment->bind_result($filename)) {
						$res = 1;
						$i=0;
						while ($statment->fetch()) {
							$messagesNumber++;
							$filesArr[$i] = $filename;
						}
						$filesArr = array_unique($filesArr);
					}
				}
			}
			$statment->close();
		}
		
		//delete all those DB lines:
		if ($messagesNumber) {
			$query = "DELETE FROM messages_data WHERE (recorder_id=$user_id and recipient_id=0) or recipient_id=$user_id";
			if ($statment = $sqlDb->stmt_init()) {
				if ($statment->prepare($query)) {
					if ($statment->execute()) {
						$rowsCahnged = $sqlDb->affected_rows;
						if ($rowsCahnged != $messagesNumber) {
							$res = 0;
						}
					}
				}
				$statment->close();
			}
		}
		//delete all relevant files, if needed:
		for ($i=0;$i<count($filesArr);$i++) {
			if ($needDelete=checkFileDeleteNeeded($sqlDb,$filesArr[$i])) {
				$deleteRes = unlink($filesArr[$i]);
				if (!$deleteRes) {
					$res = 0;
				}
			} 
		}
		return $res;
	}
	
	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$table - 'sessions' or 'messges_data' - the appropriate table.
	*				$lineIndicator - the indicator according to which the line is found in DB.
	*				$columnName - the name of the column its data we will change.
	*				$newValue - the new value of the cell.
	* @ purpose:	function updates the value of the apropriate cell in DB.
	* @ return:		1 for success and 0 if error occured.
	*/
	function updateDBcolumn($sqlDb,$table,$lineIndicator,$columnName,$newValue)
	{
		$res = 0;
		if (!strcmp($columnName,"is_read")) {
			$fullColumnName = $table.".".$columnName;
		} else {
			$fullColumnName = $columnName;
		}
		if (!strcmp($table,"sessions")) {
			$query = "UPDATE $table SET $fullColumnName=$newValue WHERE user_id=$lineIndicator";
		} elseif (!strcmp($table,"messages_data")) {
			$query = "UPDATE $table SET $fullColumnName=1,time=time WHERE messages_data.index=$lineIndicator";
		}
		if ($statment = $sqlDb->stmt_init()) {
			if ($statment->prepare($query)) {
				if ($statment->execute()) {
					if ($rowsCahnged = $sqlDb->affected_rows) {
						if ($rowsCahnged == 1) {
							$res = 1;
						}
					}
				}
			}
			$statment->close();
		}
		return $res;
	}
	

	/*
	* @ params: 	$sqlDb - mysqli database object
	*				$user_id - the id of the user who's messages we need to
	*					present on page.
	*				$messagesType - can be "received" (recieved messages), "recorded"
	*					(recorded by $user_id), "preRecorded" (for general use). This
	*					paramenter determins the query that will be sent to the SQL server.
	* @ purpose:	Function creates an SQL query, sends it, and retrieves the result
	*				(data is the messages detail).
	* @ return:		if succeeds, the function will return an array with the following data
	*				about each message: index,recorder_id,message_name,time,is_read,filename.
	*				if fails, the function returns -1.
	*/
	function createMessagesArray($sqlDb,$user_id,$messagesType)
	{
		$query = "SELECT (messages_data.index),(recorder_id),(message_name),(time),(is_read),(filename) FROM messages_data";
		if (!strcmp($messagesType,"received")) {
    		$query = $query." WHERE recipient_id=$user_id";
		} elseif (!strcmp($messagesType,"recorded")) {
			$query = $query." WHERE recorder_id=$user_id and recipient_id=0";
		} elseif (!strcmp($messagesType,"preRecorded")) {
			$query = $query." WHERE recorder_id=0 and recipient_id=0";
		} else {
			return -1;
		}
		if ($statment = $sqlDb->stmt_init()) {
			if ($statment->prepare($query)) {
				if ($statment->execute()) {
					if ($statment->bind_result($index,$recorder_id,$message_name,$time,$is_read,$filename)) {
						$i=0;
						while ($statment->fetch())
						{
							//if (!strcmp($messagesType,"received")) {
								$messagesArr[$i]['index'] = $index;
								$messagesArr[$i]['message_name'] = $message_name;
								$messagesArr[$i]['recorder_id'] = $recorder_id;
								$messagesArr[$i]['GMTtime'] = $time;
								$messagesArr[$i]['is_read'] = $is_read;
								$messagesArr[$i]['filename'] = $filename;
							/*} else {
								$messagesArr[$i]['index'] = $index;
								$messagesArr[$i]['message_name'] = $message_name;
								$messagesArr[$i]['GMTtime'] = $time;
							}*/
							$i++;
						}
						$statment->close();
						return $messagesArr;
					}
				}
			}
			$statment->close();
		}
		return -1;
	}
	

?>